using System;
using NCommet.Core.Services;
using NCommet.Core;
using NCommet.Core.Agents;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;

namespace NCommet.Modules.Authorization
{
    /// <summary>
    /// An implementation of <see cref="NCommet.Core.Agents.IAuthorizationManager" /> that stores
    /// access levels in a database table.
    /// </summary>
    /// <remarks>
    /// This implementation uses a table named ItemRole to store access level information.
    /// The item id is used to distinguish items, so this implementation requires a persister
    /// and works only with persisted items. The structure of the table follows:
    /// <table>
    /// <tr>
    /// <th>Column</th><th>Type</th><th>Description</th>
    /// </tr>
    /// <tr>
    /// <td>ItemID</td><td>int</td><td>The item id.</td>
    /// </tr>
    /// <tr>
    /// <td>RoleName</td><td>nvarchar(500</td><td>The role name.</td>
    /// </tr>
    /// <tr>
    /// <td>AccessLevel</td><td>int</td><td>The access level of the role on the item, as an integer bitmask.</td>
    /// </tr>
    /// </table>
    /// </remarks>
    public class AuthorizationManagerImpl : AuthorizationManagerBase
    {
        /// <summary>
        /// Instantiates AuthorizationManagerImpl and subscribes to <see cref="NCommet.Core.Services.EventSink.ItemDeleted"/>
        /// </summary>
        public AuthorizationManagerImpl()
        {
            EventSink.Instance.ItemDeleted += new ItemHandler(Instance_ItemDeleted);
        }

        /// <summary>
        /// Deletes entries in the ItemRole table related to the deleted item.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected virtual void Instance_ItemDeleted(object sender, ItemEventArgs e)
        {
            Db_DeleteItem(e.Item);
        }

        #region IAuthorizationManager Members

        public override bool IsAccessLevelSet(Item item, string role)
        {
            return Db_AccessLevelExists(item, role);
        }

        public override AccessLevel GetAccessLevel(Item item, string role)
        {
            AccessLevel result = AccessLevel.None;

            // Give Everything to transient objects.
            if (item.Id <= 0) return AccessLevel.Everything;

            IList<Item> path = HierarchyManager.GetAncestorPath(item, true);
            for (int i = path.Count - 1; i >= 0; i--)
                if (Db_TryGetAccessLevel(path[i], role, out result))
                    return result;
            return AccessLevel.None;
        }

        public override void SetAccessLevel(Item item, string role, AccessLevel accessLevel)
        {
            Db_SetAccessLevel(item, role, accessLevel);
        }

        public override void ResetAccessLevel(Item item, string role)
        {
            Db_DeleteAccessLevel(item, role);
        }

        public override string[] RolesWithAccess(Item item, AccessLevel accessLevelRequired)
        {
            System.Collections.Specialized.StringCollection rolesCollection = new System.Collections.Specialized.StringCollection();


            System.Collections.Specialized.StringCollection checkedRoles = new System.Collections.Specialized.StringCollection();
            IList<Item> path = HierarchyManager.GetAncestorPath(item, true);
            for (int i = path.Count - 1; i >= 0; i--)
            {
                IDictionary<string, AccessLevel> AllRoles = this.Db_GetAllRoles(path[i]);
                foreach (string role in AllRoles.Keys)
                {
                    if (!checkedRoles.Contains(role))
                    {
                        if ((AllRoles[role] & accessLevelRequired) == accessLevelRequired)
                            rolesCollection.Add(role);
                        checkedRoles.Add(role);
                    }
                }
            }

            string[] roles = new string[rolesCollection.Count];
            rolesCollection.CopyTo(roles, 0);

            return roles;
        }

        public override void RolesDeleted(string[] roles)
        {
            Db_DeleteRoles(roles);
        }

        #endregion

        #region SQL Data Access

        private SqlConnection GetConnection()
        {
            ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["MySqlConnection"];
            if (css == null)
                throw new Exception("Connection not declared");
            if (string.IsNullOrEmpty(css.ConnectionString))
                throw new Exception("Empty connection string");
            return new SqlConnection(css.ConnectionString);
        }

        private bool Db_AccessLevelExists(Item item, string role)
        {
            using (SqlConnection sql = GetConnection())
            {
                sql.Open();
                using (SqlCommand cmd = sql.CreateCommand())
                {
                    if (string.IsNullOrEmpty(role))
                    {
                        cmd.CommandText = "SELECT count(*) FROM ItemRole WHERE ItemID=@itemID AND RoleName=''";
                        cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                    }
                    else
                    {
                        cmd.CommandText = "SELECT count(*) FROM ItemRole WHERE ItemID=@itemID AND RoleName=@roleName";
                        cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                        cmd.Parameters.Add("roleName", System.Data.SqlDbType.NVarChar, 255).Value = role;
                    }
                    return Convert.ToInt32(cmd.ExecuteScalar()) > 0;
                }
            }
        }

        private AccessLevel Db_GetAccessLevel(Item item, string role)
        {
            // connet to db and find out
            AccessLevel result = AccessLevel.None;

            using (SqlConnection sql = GetConnection())
            {
                sql.Open();
                using (SqlCommand cmd = sql.CreateCommand())
                {
                    if (string.IsNullOrEmpty(role))
                    {
                        cmd.CommandText = "SELECT AccessLevel FROM ItemRole WHERE ItemID=@itemID AND RoleName=''";
                        cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                    }
                    else
                    {
                        cmd.CommandText = "SELECT AccessLevel FROM ItemRole WHERE ItemID=@itemID AND RoleName=@roleName";
                        cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                        cmd.Parameters.Add("roleName", System.Data.SqlDbType.NVarChar, 255).Value = role;
                    }
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr.Read())
                            result = (AccessLevel)dr.GetInt32(0);
                    }
                }
            }

            return result;
        }

        private bool Db_TryGetAccessLevel(Item item, string role, out AccessLevel result)
        {
            // connet to db and find out
            result = AccessLevel.None;

            using (SqlConnection sql = GetConnection())
            {
                sql.Open();
                using (SqlCommand cmd = sql.CreateCommand())
                {
                    if (string.IsNullOrEmpty(role))
                    {
                        cmd.CommandText = "SELECT AccessLevel FROM ItemRole WHERE ItemID=@itemID AND RoleName=''";
                        cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                    }
                    else
                    {
                        cmd.CommandText = "SELECT AccessLevel FROM ItemRole WHERE ItemID=@itemID AND RoleName=@roleName";
                        cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                        cmd.Parameters.Add("roleName", System.Data.SqlDbType.NVarChar, 255).Value = role;
                    }
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        if (dr.Read())
                        {
                            result = (AccessLevel)dr.GetInt32(0);
                            return true;
                        }
                        else
                            return false;
                    }
                }
            }
        }

        private void Db_SetAccessLevel(Item item, string role, AccessLevel accessLevel)
        {
            using (SqlConnection sql = GetConnection())
            {
                sql.Open();
                using (SqlCommand cmd = sql.CreateCommand())
                {
                    if (Db_AccessLevelExists(item, role))
                        cmd.CommandText = "UPDATE ItemRole SET AccessLevel=@accessLevel WHERE ItemID=@itemID AND RoleName=@roleName";
                    else
                        cmd.CommandText = "INSERT INTO ItemRole (ItemID, RoleName, AccessLevel) VALUES (@itemID, @roleName, @accessLevel)";
                    cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                    cmd.Parameters.Add("roleName", System.Data.SqlDbType.NVarChar, 255).Value = role;
                    cmd.Parameters.Add("accessLevel", System.Data.SqlDbType.Int).Value = (int)accessLevel;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private void Db_DeleteAccessLevel(Item item, string role)
        {
            using (SqlConnection sql = GetConnection())
            {
                sql.Open();
                using (SqlCommand cmd = sql.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM ItemRole WHERE ItemID=@itemID AND RoleName=@roleName";
                    cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                    cmd.Parameters.Add("roleName", System.Data.SqlDbType.NVarChar, 255).Value = role;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private IDictionary<string, AccessLevel> Db_GetAllRoles(Item item)
        {
            Dictionary<string, AccessLevel> roles = new Dictionary<string, AccessLevel>();
            // connet to db and get all roles
            using (SqlConnection sql = GetConnection())
            {
                sql.Open();
                using (SqlCommand cmd = sql.CreateCommand())
                {
                    cmd.CommandText = "SELECT RoleName, AccessLevel FROM ItemRole WHERE ItemID=@itemID";
                    cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                            roles.Add(dr["RoleName"].ToString(), (AccessLevel)Convert.ToInt32(dr["AccessLevel"]));
                    }
                }
            }

            return roles;
        }

        private void Db_DeleteRoles(string[] roles)
        {
            using (SqlConnection sql = GetConnection())
            {
                sql.Open();
                using (SqlCommand cmd = sql.CreateCommand())
                {
                    string strSql = "DELETE FROM ItemRole WHERE RoleName IN (";
                    for (int i = 0; i < roles.Length; i++)
                    {
                        strSql += ("@p" + i);
                        if (i < roles.Length - 1)
                            strSql += ", ";
                        else
                            strSql += ")";
                    }
                    cmd.CommandText = strSql;
                    for (int i = 0; i < roles.Length; i++)
                        cmd.Parameters.Add("p" + i, System.Data.SqlDbType.NVarChar, 255).Value = roles[i];
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private void Db_DeleteItem(Item item)
        {
            using (SqlConnection sql = GetConnection())
            {
                sql.Open();
                using (SqlCommand cmd = sql.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM ItemRole WHERE ItemID=@itemID";
                    cmd.Parameters.Add("itemID", System.Data.SqlDbType.Int).Value = item.Id;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion
    }
}
